angular.module('todo.service',[])
        .factory('SQLService', function($q){
            var db;
            var MB = 1024*1024;// 1MB
            var dbsize = 10+MB;// 10MB;
            // create function to create database
            function createDB(){
                try {
                    db = window.openDatabase('todointechdb','1.0', "todointechdb",dbsize);
                    db.transaction(function(tx){
                        tx.executeSql("create table if not exists tasks (task_id integer not null primary key autoincrement, task_name varchar(250))", []);
                        //tx.executeSql("drop table if exists tasks", []);
                        
                    });
                }catch (err){
                    alert("Error proccess SQL: "+err);
                }
            }
            // Create function to insert data into database table
            function setTasks(tname){
                return promisedQuery("insert into tasks (task_name) values('"+tname+"')", defaultResultHandler, defaultErrorHandler);
            }
            // Create function to delete data from database table
            function deleteTasks(tid){
                return promisedQuery("delete from tasks where task_id="+tid, defaultResultHandler, defaultErrorHandler);
            }
            // Create function to udate task in database
            function updateTasks(tname, tid){
                return promisedQuery("update tasks set task_name='"+tname+"' where task_id="+tid, defaultResultHandler, defaultErrorHandler);
            }
            // create function to select task form database
            function getTasks(){
                return promisedQuery("select * from tasks", defaultResultHandler, defaultErrorHandler);
            }
            // Create function to handle default result
            function defaultResultHandler(deferred){
                return function(tx, result){
                    var len = result.rows.length;
                    var output_result = [];
                    for(var i=0; i<len; i++){
                        var t = {'task_id': result.rows.item(i).task_id, 'task_name': result.rows.item(i).task_name};
                        output_result.push(t);
                    }
                    deferred.resolve(output_result);
                }
            }
            // Create function to handle sql error
            function defaultErrorHandler(deferred){
                return function(tx, results){
                    var len = 0;
                    var output_results = '';
                    deferred.resolve(output_results);
                }
            }
            // Create function to promised sql query
            function promisedQuery(query, successCB, errorCB){
                var deferred = $q.defer();
                db.transaction(function(tx){
                    tx.executeSql(query,[], successCB(deferred), errorCB(deferred));
                }, errorCB);
                return deferred.promise;
            }
            // Expose service
            return {
                setup: function(){
                    return createDB();
                },
                set: function(t_name){
                    return setTasks(t_name);
                },
                delete: function(taskid){
                    return deleteTasks(taskid);
                },
                edit: function(name, id){
                    return updateTasks(name, id);
                },
                all: function(){
                    return getTasks();
                }
                
                
            }
        });
